//
//  FMDBTool.swift
//  Sqlite-FDBM
//
//  Created by 朱益锋 on 15/12/24.
//  Copyright © 2015年 朱益锋. All rights reserved.
//

import UIKit
let shareDataBaseManager = DataBaseManager()
class DataBaseManager: NSObject {
    
    //创建用户存储表
    func creatUserTable(){
        let queue = FMDBHelper.getDataBaseQueue()
        //创建表
        queue.inDatabase({ (db) -> Void in
            
            /**
             *格式:create table if not exists 表名 (id inerger primary key autoincrement,字段 integer，字段 text,字段 real,字段 blob);
             */
            let sql = "create table if not exists t_userTable (id integer primary key autoincrement,name text,phone text,userId text);"
            do{
                try db.executeUpdate(sql, values: nil)
            }catch let err as NSError{
                print(err.debugDescription)
            }
        })
    }

    
    //存储用户信息
    func inserUserToDB(userInfo:UserInfo){
        let sql = "insert into t_userTable (name,phone,userId) values(?,?,?)"
        let queue = FMDBHelper.getDataBaseQueue()
        queue.inDatabase({ (db) -> Void in
            do{
                try db.executeUpdate(sql, values: [userInfo.name!,userInfo.phone!,userInfo.userId!])
            }catch let err as NSError{
                print(err.debugDescription)
            }
            
        })
    }
    func deleteUserInfo(){
        let sql = "delete from t_userTable where id > 50 and userId = 231483"
        let queue = FMDBHelper.getDataBaseQueue()
        queue.inDatabase { (db) -> Void in
            do{
                try db.executeUpdate(sql, values: nil)
                print("delete success")
            }catch let err as NSError{
                print(err.debugDescription)
            }
        }
    }
    
    
    //改变用户信息
    func updataUserInfo(name:String,phone:String,inUserId:String){
        /**
         *格式:update 表名 set 字段 = (?) 字段 = (?) where 字段 = (?) and/or 字段 = (?)
         
         
         */
        let sql = "update t_userTable set phone = (?),name = (?) where userId = (?)"
        let queue = FMDBHelper.getDataBaseQueue()
        queue.inDatabase { (db) -> Void in
            do{
                try db.executeUpdate(sql, values: [name,phone,inUserId])
            }catch let err as NSError{
                print(err.debugDescription)
            }
        }
    }
    
    //查询用户信息
    func getUserInfo()->NSMutableArray{
        
        let arr = NSMutableArray()
        /**
         *格式：select * from 表名
         *排序：order by (字段) desc(倒序) asc(默认－－升序)
         *限制数量：limit 100(数量) 或者  0,5(从0到5)
         *tip:如果要分页查询，可以设置maxID,即当前查询100～81的数据，设置MaxID为81，下次查询就从MaxID-1开始
         */
        let sql = "select * from t_userTable order by id desc "
        let queue = FMDBHelper.getDataBaseQueue()
        queue.inDatabase({ (db) -> Void in
            do{
               let resultSet = try db.executeQuery(sql, values: nil)
                while resultSet.next(){
                    let id = resultSet.intForColumn("id")
                    let name = resultSet.stringForColumn("name")
                    let phone = resultSet.stringForColumn("phone")
                    let userId = resultSet.stringForColumn("userId")
                    let dic = ["id":"\(id)","name":name,"phone":phone,"userId":userId]
                    arr.addObject(dic)
                }
            }catch let err as NSError{
                print(err.debugDescription)
            }
        })
        return arr
    }
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    

}
